package xzl.edu.cn.book.dao.impl;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import xzl.edu.cn.book.dao.BookDao;
import xzl.edu.cn.book.entity.Book;


public class BookDaoImpl extends Variable implements BookDao {

	List<Book> books = null;

	public void connection() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		this.con = DriverManager.getConnection(url, usr, password);
	}

	public void refuse() throws SQLException {
		if (this.rs != null)
			rs.close();
		if (this.ps != null)
			ps.close();
		if (this.st != null)
			st.close();
		if (this.con != null)
			con.close();
	}

	@Override
	public void add(Book pbook) {
		try {
			connection();
			this.sql = "insert into p_book(Name,price,size) values(?,?,?)";
			this.ps = con.prepareStatement(sql);
			ps.setString(1, pbook.getName());
			ps.setString(2, pbook.getPrice());
			ps.setString(3, pbook.getSize());
			ps.executeUpdate();
			refuse();
		} catch (ClassNotFoundException e) {
			logger.info("连接失败！");
			e.printStackTrace();
		} catch (SQLException e) {
			logger.info("出现异常！");
			e.printStackTrace();
		}

	}

	@Override
	public void delete(Book pbook) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			this.con = DriverManager.getConnection(url, usr, password);
			this.sql = "delete from p_book where name=?";
			this.ps = con.prepareStatement(sql);
			ps.setString(1, pbook.getName());
			ps.executeUpdate();
			refuse();
		} catch (ClassNotFoundException e) {
			logger.info("连接失败！");
			e.printStackTrace();
		} catch (SQLException e) {
			logger.info("出现异常！");
			e.printStackTrace();
		}
	}

	@Override
	public Book findByName(String name) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			this.con = DriverManager.getConnection(url, usr, password);
			this.sql = "SELECT NAME,PRICE,SIZE FROM p_book WHERE Name=?";
			this.ps = con.prepareStatement(sql);
			ps.setString(1, name);
			this.rs = ps.executeQuery();
			if (rs.next()) {
				String pName = rs.getString(1);
				String pPrice = rs.getString(2);
				String pSize = rs.getString(3);
				Book pbook = new Book(pName, pPrice, pSize);
				return pbook;
			}
			refuse();
			return null;
		} catch (ClassNotFoundException e) {
			logger.info("连接失败！");
			e.printStackTrace();
			return null;
		} catch (SQLException e) {
			logger.info("出现异常！");
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<Book> queryall() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			this.con = DriverManager.getConnection(url, usr, password);
			this.st = con.createStatement();
			this.sql = "SELECT NAME,PRICE,SIZE FROM p_book";
			this.rs = st.executeQuery(sql);
			books = new ArrayList<Book>();
			while (rs.next()) {
				Book pc = new Book();
				pc.setName(rs.getString(1));
				pc.setPrice(rs.getString(2));
				pc.setSize(rs.getString(3));
			    books.add(pc);
			}
			refuse();
		} catch (ClassNotFoundException e) {
			logger.info("连接失败！");
			e.printStackTrace();
		} catch (SQLException e) {
			logger.info("出现异常！");
			e.printStackTrace();
		}
		return books;
	}

	@Override
	public void upDate(Book pbook) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			this.con = DriverManager.getConnection(url, usr, password);
			this.sql = "UPDATE p_book SET Price=?,Size=? where Name=?";
			this.ps = con.prepareStatement(sql);
			logger.info(pbook.price);
			ps.setString(1, pbook.getPrice());
			ps.setString(2, pbook.getSize());
			ps.setString(3, pbook.getName());
			ps.execute();
			refuse();
		} catch (ClassNotFoundException e) {
			logger.info("连接失败！");
			e.printStackTrace();
		} catch (SQLException e) {
			logger.info("出现异常！");
			e.printStackTrace();
		}
	}
}
